
###################################################################
# Libraries used for this use case and exploratory data analysis
###################################################################
!pip install sodapy
!pip install geopandas
!pip install pygeos
!pip install mapclassify
import os
import time
import sys
sys.path.insert(1, '../') # so that we can import d2i_tools from the parent folder.
#from d2i_tools2 import *
import warnings
warnings.simplefilter("ignore")

from datetime import datetime, date
import numpy as np
import pandas as pd
from sodapy import Socrata
import json
import plotly.express as px
import folium
from folium.plugins import MarkerCluster
import seaborn as sns
import matplotlib.pyplot as plt
import geopandas as gpd
import shapely
import pygeos
import mapclassify
import pyproj
import requests

Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Requirement already satisfied: sodapy in /usr/local/lib/python3.7/dist-packages (2.2.0)
Requirement already satisfied: requests>=2.28.1 in /usr/local/lib/python3.7/dist-packages (from sodapy) (2.28.1)
Requirement already satisfied: charset-normalizer<3,>=2 in /usr/local/lib/python3.7/dist-packages (from requests>=2.28.1->sodapy) (2.1.1)
Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests>=2.28.1->sodapy) (2.10)
Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests>=2.28.1->sodapy) (1.24.3)
Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests>=2.28.1->sodapy) (2022.6.15)
Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Requirement already satisfied: geopandas in /usr/local/lib/python3.7/dist-packages (0.10.2)
Requirement already satisfied: pyproj>=2.2.0 in /usr/local/lib/python3.7/dist-packages (from geopandas) (3.2.1)
Requirement already satisfied: pandas>=0.25.0 in /usr/local/lib/python3.7/dist-packages (from geopandas) (1.3.5)
Requirement already satisfied: fiona>=1.8 in /usr/local/lib/python3.7/dist-packages (from geopandas) (1.8.21)
Requirement already satisfied: shapely>=1.6 in /usr/local/lib/python3.7/dist-packages (from geopandas) (1.8.4)
Requirement already satisfied: six>=1.7 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (1.15.0)
Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (2022.6.15)
Requirement already satisfied: attrs>=17 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (22.1.0)
Requirement already satisfied: click>=4.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (7.1.2)
Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (57.4.0)
Requirement already satisfied: cligj>=0.5 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (0.7.2)
Requirement already satisfied: munch in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (2.5.0)
Requirement already satisfied: click-plugins>=1.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas) (1.1.1)
Requirement already satisfied: numpy>=1.17.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (1.21.6)
Requirement already satisfied: pytz>=2017.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (2022.2.1)
Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.25.0->geopandas) (2.8.2)
Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Requirement already satisfied: pygeos in /usr/local/lib/python3.7/dist-packages (0.13)
Requirement already satisfied: numpy>=1.13 in /usr/local/lib/python3.7/dist-packages (from pygeos) (1.21.6)
Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Requirement already satisfied: mapclassify in /usr/local/lib/python3.7/dist-packages (2.4.3)
Requirement already satisfied: networkx in /usr/local/lib/python3.7/dist-packages (from mapclassify) (2.6.3)
Requirement already satisfied: numpy>=1.3 in /usr/local/lib/python3.7/dist-packages (from mapclassify) (1.21.6)
Requirement already satisfied: scikit-learn in /usr/local/lib/python3.7/dist-packages (from mapclassify) (1.0.2)
Requirement already satisfied: pandas>=1.0 in /usr/local/lib/python3.7/dist-packages (from mapclassify) (1.3.5)
Requirement already satisfied: scipy>=1.0 in /usr/local/lib/python3.7/dist-packages (from mapclassify) (1.7.3)
Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=1.0->mapclassify) (2.8.2)
Requirement already satisfied: pytz>=2017.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=1.0->mapclassify) (2022.2.1)
Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.7.3->pandas>=1.0->mapclassify) (1.15.0)
Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from scikit-learn->mapclassify) (3.1.0)
Requirement already satisfied: joblib>=0.11 in /usr/local/lib/python3.7/dist-packages (from scikit-learn->mapclassify) (1.1.0)


########################################################
# Accessing the Melbourne City Pedestrian Network Dataset
########################################################

# Hyperlink to the dataset: https://data.melbourne.vic.gov.au/Transport/Pedestrian-Network/4id4-tydi
dataset_id = '4id4-tydi' #Melbourne City Pedestrian Network dataset

apptoken = os.environ.get("SODAPY_APPTOKEN") # Anonymous app token
domain = "data.melbourne.vic.gov.au"
client = Socrata(domain, apptoken) # Open Dataset connection

WARNING:root:Requests made without an app_token will be subject to strict throttling limits.


import requests
url = 'https://data.melbourne.vic.gov.au/download/4id4-tydi/application%2Fzip'
content = requests.get(url)

# unzip the content
from io import BytesIO
from zipfile import ZipFile

f = ZipFile(BytesIO(content.content))
print(f.namelist())

['Property_centroid.json', 'Pedestrian_network.json']


#Download the json files and store locally
import zipfile, urllib.request, shutil

url = 'https://data.melbourne.vic.gov.au/download/4id4-tydi/application%2Fzip'
file_name = 'pedestriannetwork.zip'

with urllib.request.urlopen(url) as response, open(file_name, 'wb') as out_file:
    shutil.copyfileobj(response, out_file)
    with zipfile.ZipFile(file_name) as zf:
        zf.extractall()


import json
with open('Pedestrian_network.json') as file:
    pedestrianpath = json.load(file)


#Convert the JSON file to a geopandas data frame
gpd_pedestrianpath = gpd.read_file('Pedestrian_network.json')
gpd_pedestrianpath.head()


#Enhance the efficiency of plotting the dataset by filtering to 'High Traffic' footpath areas.
#Select columns
gpd_pedestrianpath_filtered = gpd_pedestrianpath[['Shape_Length', 'TRAFFIC', 'geometry']]
#Filter
gpd_pedestrianpath_filtered = gpd_pedestrianpath_filtered[gpd_pedestrianpath_filtered['TRAFFIC'].str.contains('High Traffic')]
gpd_pedestrianpath_filtered.head()


gpd_pedestrianpath.crs = {'init' :'epsg:4326'}
m = folium.Map([-37.81368709240999, 144.95738102347036], zoom_start=12)
 
folium.Choropleth(
    #gpd_pedestrianpath[gpd_pedestrianpath.geometry.length>0.0015], #Optional: select only lines above specified length to plot
    gpd_pedestrianpath_filtered,
    line_weight=3,
    line_color='blue',
    control_scale=True,
    prefer_canvas=True, 
    width=800, 
    height=580
).add_to(m)
 
m


#Read in the dataset
raw_accidents_pedestrians = pd.read_csv('https://raw.githubusercontent.com/Chameleon-company/MOP-Code/master/datascience/usecases/interactive_dependencies/Accidents_Pedestrians_Melbourne_2008to2020.csv', parse_dates=['DATAccidentDate_accident'])
raw_accidents_pedestrians.info() # see summary information of the data

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2028 entries, 0 to 2027
Data columns (total 34 columns):
 #   Column                                  Non-Null Count  Dtype         
---  ------                                  --------------  -----         
 0   KEYAccidentNumber                       2028 non-null   object        
 1   DATAccidentDate_accident                2028 non-null   datetime64[ns]
 2   TIMAccidentTime_accident                2028 non-null   object        
 3   CATAccidentTypeDesc_accident            2028 non-null   object        
 4   CATDayOfWeek_accident                   2028 non-null   object        
 5   CATDCADesc_accident                     2028 non-null   object        
 6   CATMelwaysPage_accident                 2028 non-null   object        
 7   CATMelwaysGridRef_X_accident            2028 non-null   object        
 8   CATMelwaysGridRef_Y_accident            2028 non-null   object        
 9   CATLightConditionDesc_accident          2028 non-null   object        
 10  NUMVehiclesInvolved_accident            2028 non-null   int64         
 11  NUMPersonsInvolved_accident             2028 non-null   int64         
 12  NUMPersonsInjured_accident              2028 non-null   int64         
 13  KEYPersonID_person                      2028 non-null   int64         
 14  CATRoadUserTypeDesc_person              2028 non-null   object        
 15  CATTakenHospital_person                 2028 non-null   object        
 16  CATInjuryLevelDesc_person               2028 non-null   object        
 17  CATAgeGroup_person                      2028 non-null   object        
 18  CATPostcode_person                      1454 non-null   float64       
 19  CATGender_person                        2028 non-null   object        
 20  CATLGAName_node                         2028 non-null   object        
 21  CATDEGUrbanName_node                    2028 non-null   object        
 22  NUMLatitude_node                        2028 non-null   float64       
 23  NUMLongitude_node                       2028 non-null   float64       
 24  CATPostcode_node                        2028 non-null   int64         
 25  CATSurfaceConditionDesc_surface         2028 non-null   object        
 26  CATSubDCACodeDesc_subdca                1982 non-null   object        
 27  CATAtmosphericConditionDesc_atmosphere  2028 non-null   object        
 28  CATRoadName_acclocation                 2027 non-null   object        
 29  CATRoadNameInt_acclocation              2016 non-null   object        
 30  CATRoadType_acclocation                 2021 non-null   object        
 31  CATRoadTypeInt_acclocation              2010 non-null   object        
 32  CATEventTypeDesc_accevent               2028 non-null   object        
 33  CATObjectTypeDesc_accevent              2028 non-null   object        
dtypes: datetime64[ns](1), float64(3), int64(5), object(25)
memory usage: 538.8+ KB


#Create a copy of the raw source dataset
wrk_accident_pedestrians = raw_accidents_pedestrians.copy()

#Create new features from the accident date variable such as a numerical representation of weekday name, week of the year
#day of the year and a separate variable to hold the year of accident.
wrk_accident_pedestrians['NUMDayOfWeek'] = wrk_accident_pedestrians['DATAccidentDate_accident'].dt.strftime('%w')
wrk_accident_pedestrians['NUMWeekOfYear'] = wrk_accident_pedestrians['DATAccidentDate_accident'].dt.strftime('%W')
wrk_accident_pedestrians['NUMDayOfYear'] = wrk_accident_pedestrians['DATAccidentDate_accident'].dt.strftime('%j')
wrk_accident_pedestrians['NUMYearOfAcc'] = wrk_accident_pedestrians['DATAccidentDate_accident'].dt.strftime('%Y')

#Convert the time of accident to a string and clean up excess white space
wrk_accident_pedestrians.TIMAccidentTime_accident = wrk_accident_pedestrians.TIMAccidentTime_accident.astype('string')
wrk_accident_pedestrians.TIMAccidentTime_accident = wrk_accident_pedestrians.TIMAccidentTime_accident.str.rstrip()

#Using the time of accident variable, create new features including accident hour, minute and second 
wrk_accident_pedestrians[['hour','minute','second']] = wrk_accident_pedestrians['TIMAccidentTime_accident'].astype(str).str.split(':', expand=True).astype(str)

#Create a new feature to combine the week day name and hour of accident
wrk_accident_pedestrians['CATWeekDayHour'] = wrk_accident_pedestrians[['CATDayOfWeek_accident', 'hour']].agg(' '.join, axis=1)

#Set the time format for the time of accident variable
wrk_accident_pedestrians['TIMAccidentTime_accident'] = pd.to_datetime(wrk_accident_pedestrians['TIMAccidentTime_accident'], format='%H:%M:%S').dt.time

#Clean up the text white space in the DCA description variable
wrk_accident_pedestrians.CATDCADesc_accident = wrk_accident_pedestrians.CATDCADesc_accident.str.rstrip()

#Create and apply a group mapping for the hour of accident
mapping = {'00': 'Early Morning', '01': 'Early Morning', '02': 'Early Morning', '03': 'Early Morning', '04': 'Early Morning', '05': 'Early Morning',
           '06': 'Morning', '07': 'Morning', '08': 'Morning',  '09': 'Late Morning',  '10': 'Late Morning', '11': 'Late Morning',
           '12': 'Early Afternoon', '13': 'Early Afternoon', '14':'Early Afternoon',  '15': 'Late Afternoon', '16': 'Late Afternoon',
           '17': 'Evening', '18': 'Evening', '19': 'Evening', '20': 'Night', '21': 'Night', '22': 'Night', '23': 'Night' }
wrk_accident_pedestrians['hourgroup'] = wrk_accident_pedestrians.hour.map(mapping)

#Create a new feature which concatenates the week day name and accident hour group mapping
wrk_accident_pedestrians['CATWeekDayHourGroup'] = wrk_accident_pedestrians[['CATDayOfWeek_accident', 'hourgroup']].agg(' '.join, axis=1)

#Convert all categorical variables to strings
wrk_accident_pedestrians.CATAccidentTypeDesc_accident = wrk_accident_pedestrians.CATAccidentTypeDesc_accident.astype('string')
wrk_accident_pedestrians['CATDayOfWeek_accident'] = wrk_accident_pedestrians['CATDayOfWeek_accident'].astype('string')
wrk_accident_pedestrians['CATDCADesc_accident'] = wrk_accident_pedestrians['CATDCADesc_accident'].astype('string')
wrk_accident_pedestrians['CATMelwaysPage_accident'] = wrk_accident_pedestrians['CATMelwaysPage_accident'].astype('string')
wrk_accident_pedestrians['CATMelwaysGridRef_X_accident'] = wrk_accident_pedestrians['CATMelwaysGridRef_X_accident'].astype('string')
wrk_accident_pedestrians['CATMelwaysGridRef_Y_accident'] = wrk_accident_pedestrians['CATMelwaysGridRef_Y_accident'].astype('string')
wrk_accident_pedestrians['CATLightConditionDesc_accident'] = wrk_accident_pedestrians['CATLightConditionDesc_accident'].astype('string')
wrk_accident_pedestrians['CATRoadUserTypeDesc_person'] = wrk_accident_pedestrians['CATRoadUserTypeDesc_person'].astype('string')
wrk_accident_pedestrians['CATTakenHospital_person'] = wrk_accident_pedestrians['CATTakenHospital_person'].astype('string')
wrk_accident_pedestrians['CATInjuryLevelDesc_person'] = wrk_accident_pedestrians['CATInjuryLevelDesc_person'].astype('string')
wrk_accident_pedestrians['CATAgeGroup_person'] = wrk_accident_pedestrians['CATAgeGroup_person'].astype('string')
wrk_accident_pedestrians['CATPostcode_person'] = wrk_accident_pedestrians['CATPostcode_person'].astype('string')
wrk_accident_pedestrians['CATGender_person'] = wrk_accident_pedestrians['CATGender_person'].astype('string')
wrk_accident_pedestrians['CATLGAName_node'] = wrk_accident_pedestrians['CATLGAName_node'].astype('string')
wrk_accident_pedestrians['CATDEGUrbanName_node'] = wrk_accident_pedestrians['CATDEGUrbanName_node'].astype('string')
wrk_accident_pedestrians['CATPostcode_node'] = wrk_accident_pedestrians['CATPostcode_node'].astype('string')
wrk_accident_pedestrians['CATSurfaceConditionDesc_surface'] = wrk_accident_pedestrians['CATSurfaceConditionDesc_surface'].astype('string')
wrk_accident_pedestrians['CATSubDCACodeDesc_subdca'] = wrk_accident_pedestrians['CATSubDCACodeDesc_subdca'].astype('string')
wrk_accident_pedestrians['CATAtmosphericConditionDesc_atmosphere'] = wrk_accident_pedestrians['CATAtmosphericConditionDesc_atmosphere'].astype('string')
wrk_accident_pedestrians['CATRoadName_acclocation'] = wrk_accident_pedestrians['CATRoadName_acclocation'].astype('string')
wrk_accident_pedestrians['CATRoadNameInt_acclocation'] = wrk_accident_pedestrians['CATRoadNameInt_acclocation'].astype('string')
wrk_accident_pedestrians['CATRoadType_acclocation'] = wrk_accident_pedestrians['CATRoadType_acclocation'].astype('string')
wrk_accident_pedestrians['CATRoadTypeInt_acclocation'] = wrk_accident_pedestrians['CATRoadTypeInt_acclocation'].astype('string')
wrk_accident_pedestrians['CATEventTypeDesc_accevent'] = wrk_accident_pedestrians['CATEventTypeDesc_accevent'].astype('string')
wrk_accident_pedestrians['CATObjectTypeDesc_accevent'] = wrk_accident_pedestrians['CATObjectTypeDesc_accevent'].astype('string')

#Create a new feature which concatenates the accident road name and type variables
wrk_accident_pedestrians['CATAccidentRoadGroup'] = wrk_accident_pedestrians['CATRoadName_acclocation'].fillna('') + ' ' + wrk_accident_pedestrians['CATRoadType_acclocation'].fillna('')

#Convert all numerical variables to integer, except for longitude and latitude which will remain as a floating point.
wrk_accident_pedestrians['NUMVehiclesInvolved_accident'] = wrk_accident_pedestrians['NUMVehiclesInvolved_accident'].astype(int)
wrk_accident_pedestrians['NUMPersonsInvolved_accident'] = wrk_accident_pedestrians['NUMPersonsInvolved_accident'].astype(int)
wrk_accident_pedestrians['NUMPersonsInjured_accident'] = wrk_accident_pedestrians['NUMPersonsInjured_accident'].astype(int)
wrk_accident_pedestrians['NUMRecordCount'] = 1

#Print the information summary for the working dataset
wrk_accident_pedestrians.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2028 entries, 0 to 2027
Data columns (total 46 columns):
 #   Column                                  Non-Null Count  Dtype         
---  ------                                  --------------  -----         
 0   KEYAccidentNumber                       2028 non-null   object        
 1   DATAccidentDate_accident                2028 non-null   datetime64[ns]
 2   TIMAccidentTime_accident                2028 non-null   object        
 3   CATAccidentTypeDesc_accident            2028 non-null   string        
 4   CATDayOfWeek_accident                   2028 non-null   string        
 5   CATDCADesc_accident                     2028 non-null   string        
 6   CATMelwaysPage_accident                 2028 non-null   string        
 7   CATMelwaysGridRef_X_accident            2028 non-null   string        
 8   CATMelwaysGridRef_Y_accident            2028 non-null   string        
 9   CATLightConditionDesc_accident          2028 non-null   string        
 10  NUMVehiclesInvolved_accident            2028 non-null   int64         
 11  NUMPersonsInvolved_accident             2028 non-null   int64         
 12  NUMPersonsInjured_accident              2028 non-null   int64         
 13  KEYPersonID_person                      2028 non-null   int64         
 14  CATRoadUserTypeDesc_person              2028 non-null   string        
 15  CATTakenHospital_person                 2028 non-null   string        
 16  CATInjuryLevelDesc_person               2028 non-null   string        
 17  CATAgeGroup_person                      2028 non-null   string        
 18  CATPostcode_person                      1454 non-null   string        
 19  CATGender_person                        2028 non-null   string        
 20  CATLGAName_node                         2028 non-null   string        
 21  CATDEGUrbanName_node                    2028 non-null   string        
 22  NUMLatitude_node                        2028 non-null   float64       
 23  NUMLongitude_node                       2028 non-null   float64       
 24  CATPostcode_node                        2028 non-null   string        
 25  CATSurfaceConditionDesc_surface         2028 non-null   string        
 26  CATSubDCACodeDesc_subdca                1982 non-null   string        
 27  CATAtmosphericConditionDesc_atmosphere  2028 non-null   string        
 28  CATRoadName_acclocation                 2027 non-null   string        
 29  CATRoadNameInt_acclocation              2016 non-null   string        
 30  CATRoadType_acclocation                 2021 non-null   string        
 31  CATRoadTypeInt_acclocation              2010 non-null   string        
 32  CATEventTypeDesc_accevent               2028 non-null   string        
 33  CATObjectTypeDesc_accevent              2028 non-null   string        
 34  NUMDayOfWeek                            2028 non-null   object        
 35  NUMWeekOfYear                           2028 non-null   object        
 36  NUMDayOfYear                            2028 non-null   object        
 37  NUMYearOfAcc                            2028 non-null   object        
 38  hour                                    2028 non-null   object        
 39  minute                                  2028 non-null   object        
 40  second                                  2028 non-null   object        
 41  CATWeekDayHour                          2028 non-null   object        
 42  hourgroup                               2028 non-null   object        
 43  CATWeekDayHourGroup                     2028 non-null   object        
 44  CATAccidentRoadGroup                    2028 non-null   string        
 45  NUMRecordCount                          2028 non-null   int64         
dtypes: datetime64[ns](1), float64(2), int64(5), object(12), string(26)
memory usage: 728.9+ KB


import folium
from folium.plugins import MarkerCluster
def map_visualization(data):
    locations = []
    for i in range(len(data)):
        row =data.iloc[i]
        location = [(row.NUMLatitude_node,row.NUMLongitude_node)]*int(row.NUMRecordCount)
        locations += location
      
    marker_cluster  = MarkerCluster(
      locations=locations,
      overlay=True,
      control=True,
      )
    m = folium.Map(location=[-37.81368709240999, 144.95738102347036], tiles="Cartodb Positron", zoom_start=13)
    marker_cluster.add_to(m)

    folium.LayerControl().add_to(m)
    
    m
    return m
map_visualization(wrk_accident_pedestrians)


rain_df = pd.read_csv('rain/IDCJAC0009_086232_1800/IDCJAC0009_086232_1800_Data.csv')
#Creating a data column
dates = [date(year, month, day)for (year,month,day) in zip(rain_df.Year, rain_df.Month, rain_df.Day)]
rain_df['date'] = pd.to_datetime(dates)
rain_df = rain_df[(rain_df['date'].dt.year>=2008) & (rain_df['date'].dt.year<=2020)]
#Droping missing columns
rain_df = rain_df[['date','Rainfall amount (millimetres)']].dropna()


#Merging daily accidents count with rain data
daily_accidents_and_rain = pd.merge(rain_df, wrk_accident_pedestrians_daygrp, left_on='date', right_on='index', how='left')
daily_accidents_and_rain = daily_accidents_and_rain.drop('index', axis=1).rename(columns={'DATAccidentDate_accident':'accidents'})
#Replacing NaN by 0 in accidents column, since it represents days that there were no accidents.
daily_accidents_and_rain['accidents'] = daily_accidents_and_rain['accidents'].fillna(0)
#Creating a categorical column to indicate of it rained or not 
daily_accidents_and_rain['rained'] = daily_accidents_and_rain['Rainfall amount (millimetres)'].apply(lambda x: 'Yes' if x>1 else 'No')
#Visualizing the boxplot of rainy and non rainy days
sns.boxplot(daily_accidents_and_rain, x = 'rained', y='accidents').set(
    xlabel='Rainy day', 
    ylabel='Accidents'
)
plt.show()

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-26-ac2b0e6ac015> in <module>
      1 #Merging daily accidents count with rain data
----> 2 daily_accidents_and_rain = pd.merge(rain_df, wrk_accident_pedestrians_daygrp, left_on='date', right_on='index', how='left')
      3 daily_accidents_and_rain = daily_accidents_and_rain.drop('index', axis=1).rename(columns={'DATAccidentDate_accident':'accidents'})
      4 #Replacing NaN by 0 in accidents column, since it represents days that there were no accidents.
      5 daily_accidents_and_rain['accidents'] = daily_accidents_and_rain['accidents'].fillna(0)

NameError: name 'rain_df' is not defined


def find_relationship(df, field1='CATInjuryLevelDesc_person', field2=None, normalize=True):
    '''
    Creates a matrix showing the relative frequency of each category of field 1 against field 2
    '''
    result = pd.DataFrame(df[field1].unique()).rename(columns={0:field2}).set_index(field2)
    for condition in df[field2].unique():
        #print(condition)
        percentage = pd.DataFrame(df[df[field2]==condition][field1].value_counts(normalize=normalize)).rename(columns={field1:condition})
        result = pd.merge(result, percentage, left_index=True, right_index=True, how='left')
    return result.fillna(0).T


frequency_by_age_group = find_relationship(wrk_accident_pedestrians, 'CATInjuryLevelDesc_person', 'CATAgeGroup_person').sort_values('Serious injury', ascending=False)
fig, ax = plt.subplots(figsize=(10,4))
ax.bar(range(len(frequency_by_age_group)),list(reversed(list(frequency_by_age_group['Serious injury']))), tick_label=list(reversed(frequency_by_age_group.index)))
ax.tick_params(axis='both', which='major', labelsize=10)
ax.set_title('Relative frequency of serious injury accident by age', fontsize=14)
plt.show()


frequency_by_weekday = find_relationship(wrk_accident_pedestrians, 'CATInjuryLevelDesc_person', 'CATDayOfWeek_accident').sort_values('Serious injury', ascending=False)
fig, ax = plt.subplots(figsize=(10,4))
ax.bar(range(len(frequency_by_weekday)),list(reversed(list(frequency_by_weekday['Serious injury']))), tick_label=list(reversed(frequency_by_weekday.index)))
ax.tick_params(axis='both', which='major', labelsize=10)
ax.set_title('Relative frequency of serious injury accident by day of the week', fontsize=14)
plt.show()


wrk_accident_pedestrians.groupby('CATDayOfWeek_accident').agg('mean')[['NUMPersonsInvolved_accident','NUMPersonsInjured_accident']]


frequency_by_daytime = find_relationship(wrk_accident_pedestrians, 'CATInjuryLevelDesc_person', 'hourgroup').sort_values('Serious injury', ascending=False)
fig, ax = plt.subplots(figsize=(12,4))
ax.bar(range(len(frequency_by_daytime)),list(reversed(list(frequency_by_daytime['Serious injury']))), tick_label=list(reversed(frequency_by_daytime.index)))
ax.tick_params(axis='both', which='major', labelsize=10)
ax.set_title('Relative frequency of serious injury accident by time of day', fontsize=14)
plt.show()


frequency_by_road = find_relationship(wrk_accident_pedestrians, 'CATInjuryLevelDesc_person', 'CATAccidentRoadGroup', normalize=False).sort_values('Serious injury', ascending=False).iloc[:10]
frequency_by_road


#Creating a GeoDataFrame
wrk_accident_pedestrians_gdf = gpd.GeoDataFrame(wrk_accident_pedestrians, geometry=gpd.points_from_xy(wrk_accident_pedestrians['NUMLongitude_node'], wrk_accident_pedestrians['NUMLatitude_node']))
wrk_accident_pedestrians_gdf = wrk_accident_pedestrians_gdf.set_crs( pyproj.CRS.from_user_input('EPSG:4326'))


def create_grid(gdf, n_cells=15):

    '''
    Creates a regular grid over the extent of gdf

    Returns:
        A GeoDataFrame with the cells geometries
    '''
    # total area for the grid
    xmin, ymin, xmax, ymax= gdf.total_bounds
    # how many cells across and down
    cell_size = (xmax-xmin)/n_cells
    # projection of the grid
    #crs = "+proj=sinu +lon_0=0 +x_0=0 +y_0=0 +a=6371007.181 +b=6371007.181 +units=m +no_defs"
    crs = gdf.crs
    # create the cells in a loop
    grid_cells = []
    for x0 in np.arange(xmin, xmax+cell_size, cell_size ):
        for y0 in np.arange(ymin, ymax+cell_size, cell_size):
            # bounds
            x1 = x0-cell_size
            y1 = y0+cell_size
            grid_cells.append( shapely.geometry.box(x0, y0, x1, y1)  )
    grid = gpd.GeoDataFrame(grid_cells, columns=['geometry'], 
                                    crs=crs)
    return grid


def summarize_within(input_gdf, input_summary_gdf, in_fields, out_fields = None, aggfunc='mean'):
    '''
    Overlays a polygon layer with another layer to calculate attribute field statistics about those features (input_summary_gdf) within the polygons (input_gdf).

    Parameters:
        input_gdf: Geodataframe of the polygons in which features will be summarized by.
        input_summary_gdf: Geodataframe of features that will be summarized
        in_fields: name of the fields (in input_summary_gdf) that will be summarized
        out_fields: name that the fields will have after they're summarized
        aggfunc: function that will be used to summarize
    
    Returns:
        A geodataframe with 'input_gdf' polygons and the attributes of 'input_summary_gdf' summarized by each polygon.

    '''
    input_gdf = input_gdf.copy()
    input_summary_gdf = input_summary_gdf.copy()
    if out_fields == None:
        out_fields = in_fields
    #Merges the dwelling points with the input_polygons. A new column "index right" is created. It indicates in what cell the property is within.
    merged = gpd.sjoin(input_summary_gdf, input_gdf, how='left')
    #Now lets count how many properties are within each cell
    dissolve = merged.dissolve(by="index_right", aggfunc=aggfunc) #Dissolve (looks like groupby) by the cell index
    for in_field, out_field in zip(in_fields, out_fields):
        input_gdf.loc[dissolve.index, out_field] = dissolve[in_field].values #Putting number of properties in input_polygons gdf

    return input_gdf.round(2)
    #input_polygons = input_polygons.dropna().round(2)


#Adds boolean columns that indicates if the injury was serious or not
wrk_accident_pedestrians_gdf['Serious injury'] = wrk_accident_pedestrians['CATInjuryLevelDesc_person'].apply(
    lambda level: 1 if level in ['Serious injury', 'Fatality'] else 0)

wrk_accident_pedestrians_gdf['No Serious injury'] = wrk_accident_pedestrians['CATInjuryLevelDesc_person'].apply(
    lambda level: 0 if level in ['Serious injury', 'Fatality'] else 1)


#Creates a regular grid
grid = create_grid(wrk_accident_pedestrians_gdf)
#Summarizes accidents within each grid
summarized_grid = summarize_within(grid ,
    wrk_accident_pedestrians_gdf, 
    in_fields= ['Serious injury','No Serious injury'], 
    aggfunc='sum')
summarized_grid['Serious injury percentage'] = summarized_grid['Serious injury']/(summarized_grid['Serious injury'] + summarized_grid['No Serious injury'])
summarized_grid = summarized_grid.set_crs( pyproj.CRS.from_user_input('EPSG:4326'))
#Considers only grids with more then 1 serious and not serious accidents
summarized_grid = summarized_grid[(summarized_grid['Serious injury']>1) & (summarized_grid['No Serious injury']>1)]


summarized_grid.explore(
     column='Serious injury percentage', # make choropleth based on "BoroName" column
     tooltip=[el for el in summarized_grid.columns if el!='geometry'], # show "BoroName" value in tooltip (on hover)
     scheme="naturalbreaks",
     popup=True, # show all values in popup (on click)
     tiles="CartoDB positron", # use "CartoDB positron" tiles
     cmap="Reds", # use "Set1" matplotlib colormap
     style_kwds=dict(color="black") # use black outline,
    )


!jupyter nbconvert --to html usecase-pedestriansafety-part1.ipynb

[NbConvertApp] Converting notebook usecase-pedestriansafety-part1.ipynb to html
[NbConvertApp] Writing 98145854 bytes to usecase-pedestriansafety-part1.html

	OBJECTID	NETID	TYPE	MCCID	COST	Shape_Length	DESCRIPTION	TRAFFIC	geometry
0	1	1	1	1389774	1.856134	123.742235	Pestrian Footpath	High Traffic	LINESTRING (144.98254 -37.84522, 144.98392 -37...
1	2	2	1	1389774	0.031922	2.128135	Pestrian Footpath	High Traffic	LINESTRING (144.98042 -37.84496, 144.98041 -37...
2	3	3	1	1468181	3.098050	206.536685	Pestrian Footpath	Low Traffic	LINESTRING (144.98041 -37.84494, 144.97973 -37...
3	4	4	1	0	1.408645	93.909699	Pestrian Footpath	Low Traffic	LINESTRING (144.98475 -37.84455, 144.98493 -37...
4	5	5	1	0	0.515907	34.393783	Pestrian Footpath	Low Traffic	LINESTRING (144.98531 -37.84377, 144.98493 -37...

	NUMPersonsInvolved_accident	NUMPersonsInjured_accident
CATDayOfWeek_accident
Friday	2.294618	0.365439
Monday	2.329412	0.423529
Saturday	2.458182	0.410909
Sunday	2.846491	0.697368
Thursday	2.267101	0.397394
Tuesday	2.215613	0.364312
Wednesday	2.299120	0.460411

CATAccidentRoadGroup	Other injury	Serious injury	Not injured	Fatality
ST KILDA ROAD	51	46	3	1
ELIZABETH STREET	80	37	9	0
FLINDERS STREET	35	35	1	0
LONSDALE STREET	66	32	3	0
COLLINS STREET	61	30	4	0
SPENCER STREET	55	25	2	1
KING STREET	51	25	5	1
RACECOURSE ROAD	28	20	2	1
FLEMINGTON ROAD	19	19	2	0
CLARENDON STREET	30	18	2	0

Checking in what conditions serious injury accidents happens¶